package geektime.spring.data.druiddemo;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.DataSource;

@SpringBootApplication
@Slf4j
public class DruidDemoApplication implements CommandLineRunner {
	@Autowired
	private DataSource dataSource;

	public static void main(String[] args) {
		SpringApplication.run(DruidDemoApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		log.info(dataSource.toString());
		Connection conn = dataSource.getConnection();
		String sql = "";
		PreparedStatement statement = null;
		ResultSet rs = null;

		// 第1种情况,拼接SQL
		sql = "select name from t_coffee where id = 1 or 1=1";
		statement = conn.prepareStatement(sql);
		rs = statement.executeQuery();

		// 第2种情况,参数,非拼接
		sql = "select name from t_coffee where id=? or 1=1";
		statement = conn.prepareStatement(sql);
		statement.setInt(1, 1);
		rs = statement.executeQuery();

		// 第3种情况,in条件带两个值 
		sql = "select name from t_coffee where id in(?,?)";
		statement = conn.prepareStatement(sql);
		statement.setInt(1, 1);
		statement.setInt(2, 2);
		rs = statement.executeQuery();

		// 第4种情况,in条件带超10个值
		sql = "select name from t_coffee where id in(?,?,?,?,?,?,?,?,?,?,?)";
		statement = conn.prepareStatement(sql);
		statement.setInt(1, 1);
		statement.setInt(2, 2);
		statement.setInt(3, 3);
		statement.setInt(4, 4);
		statement.setInt(5, 5);
		statement.setInt(6, 6);
		statement.setInt(7, 7);
		statement.setInt(8, 8);
		statement.setInt(9, 9);
		statement.setInt(10, 10);
		statement.setInt(11, 11);
		rs = statement.executeQuery();

		// 第5种情况,多条语句不能执行
		sql = "select name from t_coffee where id = ?; select name from t_coffee where id = 2";
		statement = conn.prepareStatement(sql);
		statement.setInt(1, 1);
		rs = statement.executeQuery();

		while (rs.next()) {
			log.info("coffee name -->{}", rs.getString("name"));
		}
	}
}
